with interceptor_network as ( --网点异常 --存在维护的网点名称和网点表里的网点名称不一致
    select network_code
    from (--网点剔除+三段码剔除
             select t1.network_code
             from (
                      select t1.network_code
                           , if(t1.effect_time_start is null, '1999-01-01 01:01:01',
                                t1.effect_time_start) as effect_time_start
                           , if(t1.effect_time_end is null, '9999-01-01 01:01:01',
                                t1.effect_time_end)   as effect_time_end
                      from jms_dim.dim_yl_oms_order_interceptor_base_dt t1
                      where dt = '{{ execution_date | cst_ds }}'
                        and t1.data_type = 'network'
                        and t1.is_delete = 0           --未删除
                        and t1.is_enable = 1           --已启用
                        and t1.network_code is not null
                        and ((t1.effect_time_start is not null and t1.effect_time_end is not null)
                          or (t1.effect_time_start is null and t1.effect_time_end is null and
                              t1.effect_time_type = 0))--生效时间类型 0 无限 ，1 有效时间断
                      group by t1.network_code
                             , t1.effect_time_start
                             , t1.effect_time_end
                  ) t1
             where '{{ execution_date | cst_ds }}' >= substr(effect_time_start, 1, 10)
               and '{{ execution_date | cst_ds }}' <= substr(effect_time_end, 1, 10)
             union all
             select network_code
             from (
                      select network_code
                           , if(t1.effect_time_start is null, '1999-01-01 01:01:01',
                                t1.effect_time_start) as effect_time_start
                           , if(t1.effect_time_end is null, '9999-01-01 01:01:01',
                                t1.effect_time_end)   as effect_time_end
                      from jms_dim.dim_yl_oms_order_interceptor_base_dt t1
                      where dt = '{{ execution_date | cst_ds }}'
                        and data_type = 'the_third_segment_code'
                        and is_delete = 0           --未删除
                        and is_enable = 1           --已启用
                        and (the_third_code is not null and network_code is not null)
                        and ((effect_time_start is not null and effect_time_end is not null)
                          or (effect_time_start is null and effect_time_end is null and
                              effect_time_type = 0))--生效时间类型 0 无限 ，1 有效时间断
                      group by network_code
                             , network_name
                             , the_third_code
                             , effect_time_start
                             , effect_time_end
                  ) a
             where '{{ execution_date | cst_ds }}' >= substr(effect_time_start, 1, 10)
               and '{{ execution_date | cst_ds }}' <= substr(effect_time_end, 1, 10)
         ) a
    group by network_code
),
     other_table as (
         select is_delay
              , is_special_report
              , network_code
              , network_name
         from jms_dim.dim_network_abnormal_type_union_dt
         where dt = '{{ execution_date | cst_ds }}'
           and (is_delay = 1 or is_special_report = 1)
     )
insert
overwrite
table
jms_dm.dm_mng_explosion_warehouse_network_punish_dt
partition
(
dt
)
select t1.final_sign_network_code   --派件网点编码
     , t1.final_sign_network_name   --派件网点名称
     , t1.end_agent_code            --所属代理区编码
     , t1.end_agent_name            --所属代理区名称
     , t1.end_provider_id           --所属省份id
     , t1.end_provider_name         --所属省份名称
     , t1.need_sign_count_1d        --1天应签收
     , t1.retention_count_1d        --1天内滞留
     , t1.need_sign_count_2d        --2天应签收
     , t1.retention_count_2d        --2天内滞留
     , t1.need_sign_count_3d        --3天应签收
     , t1.retention_count_3d        --3天内滞留
     , t1.need_sign_count_5d        --5天应签收
     , t1.retention_count_5d        --5天内滞留
     , t1.need_sign_count_7d        --7天应签收
     , t1.retention_count_7d        --7天内滞留
     , t1.scan_sum                  --时效签收量揽签分布汇总表
     , t1.final_plan_sign_date      --规划签收日期
     , t1.need_sign_count           --零班票量  时效应签收量新末端签收汇总表(规划签收)
     , t1.staff_code                --员工编码
     , t1.staff_name                --员工名称
     , t1.position_name             --岗位名称.
     , t1.virt_code                 --虚拟代理区code
     , t1.virt_name                 --虚拟代理区name
     , '{{ execution_date | cst_ds }}' as date_time
     , t1.end_city_id               --末端网点所属城市id
     , t1.end_city_name             --末端网点所属城市名称
     , t1.district_code
     , t1.district_desc
     , t1.district_id
     , null         as frequency30  --30天内爆仓天数
     , null         as duration     --60天内持续天数
     , null         as forfeit      --当日罚金
     , null         as forfeitsum   --累计罚款金额
     , null         as forfeitsum30 --30天内累计罚金
     , null         as more_3ctn_30 --30天内连续等于3的次数
     , null         as more_3ctn_60 --60天内连续等于3的次数
     , t1.area_code
     , t1.area_name
     , 0            as button_delete
     , t1.three_pre_flag
     , t1.five_pre_flag
     , t1.seven_pre_flag
     , t1.yali_flag
     , t1.zero_flag
     , '{{ execution_date | cst_ds }}' as dt
from jms_dm.dm_mng_explosion_warehouse_network_punish_mid_dt t1
         left anti
         join interceptor_network t2
              on t1.final_sign_network_code = t2.network_code
         left anti
         join other_table t3
              on t1.final_sign_network_code = t3.network_code
--          left anti
--          join abnormal_event t3
--               on t1.final_sign_network_code = t3.network_code
where t1.dt = '{{ execution_date | cst_ds }}'
  and yali_flag = 1
  and zero_flag = 0
    distribute by dt
;
--汇总

insert
    overwrite
    table jms_dm.dm_mng_explosion_warehouse_network_punish_dt
    partition
    (dt)
select t1.final_sign_network_code                             --派件网点编码
     , t1.final_sign_network_name                             --派件网点名称
     , t1.end_agent_code                                      --所属代理区编码
     , t1.end_agent_name                                      --所属代理区名称
     , t1.end_provider_id                                     --所属省份id
     , t1.end_provider_name                                   --所属省份名称
     , t1.need_sign_count_1d                                  --1天应签收
     , t1.retention_count_1d                                  --1天内滞留
     , t1.need_sign_count_2d                                  --2天应签收
     , t1.retention_count_2d                                  --2天内滞留
     , t1.need_sign_count_3d                                  --3天应签收
     , t1.retention_count_3d                                  --3天内滞留
     , t1.need_sign_count_5d                                  --5天应签收
     , t1.retention_count_5d                                  --5天内滞留
     , t1.need_sign_count_7d                                  --7天应签收
     , t1.retention_count_7d                                  --7天内滞留
     , t1.scan_sum                                            --时效签收量揽签分布汇总表
     , t1.final_plan_sign_date                                --规划签收日期
     , t1.need_sign_count                                     --零班票量  时效应签收量新末端签收汇总表(规划签收)
     , t1.staff_code                                          --员工编码
     , t1.staff_name                                          --员工名称
     , t1.position_name                                       --岗位名称.
     , t1.virt_code                                           --虚拟代理区code
     , t1.virt_name                                           --虚拟代理区name
     , date_add('{{ execution_date | cst_ds }}', -1)            as date_time
     , t1.end_city_id                                         --末端网点所属城市id
     , t1.end_city_name                                       --末端网点所属城市名称
     , t1.district_code
     , t1.district_desc
     , t1.district_id
     , t1.frequency30                                         --30天内爆仓天数
     , t1.duration                                            --60天内持续天数
     , t1.forfeit                                             --当日罚金
     , t1.forfeitsum                                          --累计罚款金额
     , t1.forfeitsum30                                        --30天内累计罚金
     , t1.more_3ctn_30                                        --30天内连续等于3的次数
     , t1.more_3ctn_60                                        --60天内连续等于3的次数
     , t1.area_code
     , t1.area_name
     , if(t2.network_code is not null, 1, 0) as button_delete --1代表手动删除,0代表不删出
     , t1.three_pre_flag
     , t1.five_pre_flag
     , t1.seven_pre_flag
     , t1.yali_flag
     , t1.zero_flag
     , date_add('{{ execution_date | cst_ds }}', -1)            as dt
from (
         select *
         from jms_dm.dm_mng_explosion_warehouse_network_punish_dt t1
         where dt = date_add('{{ execution_date | cst_ds }}', -1)
     ) t1
         left join
     (
         select date_time
              , network_code
              , status
         from jms_dim.dim_tab_stay_warning_config_base_dt
         where dt = '{{ execution_date | cst_ds }}'
           and substr(date_time, 1, 10) = date_add('{{ execution_date | cst_ds }}', -1)
           and status = 1
     ) t2
     on t1.final_sign_network_code = t2.network_code
    distribute by dt
;
--汇总

insert
    overwrite
    table jms_dm.dm_mng_explosion_warehouse_network_punish_dt
    partition
    (dt)
select t1.final_sign_network_code                                                             --派件网点编码
     , t1.final_sign_network_name                                                             --派件网点名称
     , t1.end_agent_code                                                                      --所属代理区编码
     , t1.end_agent_name                                                                      --所属代理区名称
     , t1.end_provider_id                                                                     --所属省份id
     , t1.end_provider_name                                                                   --所属省份名称
     , t1.need_sign_count_1d                                                                  --1天应签收
     , t1.retention_count_1d                                                                  --1天内滞留
     , t1.need_sign_count_2d                                                                  --2天应签收
     , t1.retention_count_2d                                                                  --2天内滞留
     , t1.need_sign_count_3d                                                                  --3天应签收
     , t1.retention_count_3d                                                                  --3天内滞留
     , t1.need_sign_count_5d                                                                  --5天应签收
     , t1.retention_count_5d                                                                  --5天内滞留
     , t1.need_sign_count_7d                                                                  --7天应签收
     , t1.retention_count_7d                                                                  --7天内滞留
     , t1.scan_sum                                                                            --时效签收量揽签分布汇总表
     , t1.final_plan_sign_date                                                                --规划签收日期
     , t1.need_sign_count                                                                     --零班票量  时效应签收量新末端签收汇总表(规划签收)
     , t1.staff_code                                                                          --员工编码
     , t1.staff_name                                                                          --员工名称
     , t1.position_name                                                                       --岗位名称.
     , t1.virt_code                                                                           --虚拟代理区code
     , t1.virt_name                                                                           --虚拟代理区name
     , t1.date_time
     , t1.end_city_id                                                                         --末端网点所属城市id
     , t1.end_city_name                                                                       --末端网点所属城市名称
     , t1.district_code
     , t1.district_desc
     , t1.district_id
     , t2.frequency30                                                         as frequency30  --30天内爆仓天数
     , t3.duration                                                            as duration     --60天内持续天数
     , case when t3.duration - 2 > 0 then 1000 else 0 end                     as forfeit      --当日罚金
     , case when t3.duration - 2 > 0 then (t3.duration - 2) * 1000 else 0 end as forfeitsum   --累计罚款金额
     , t2.forfeitSum30                                                        as forfeitsum30 --30天内累计罚金
     , t2.more_3ctn_30                                                        as more_3ctn_30 --30天内连续大于等于3的次数
     , t3.more_3ctn_60                                                        as more_3ctn_60 --60天内连续大于等于3的次数
     , t1.area_code
     , t1.area_name
     , t1.button_delete
     , t1.three_pre_flag
     , t1.five_pre_flag
     , t1.seven_pre_flag
     , t1.yali_flag
     , t1.zero_flag
     , t1.dt
from (
         select *
         from jms_dm.dm_mng_explosion_warehouse_network_punish_dt
         where dt = '{{ execution_date | cst_ds }}'
           and final_sign_network_code is not null
           and button_delete = 0
     ) t1
         left join
     (
         select final_sign_network_code
              , frequency30
              , sum(if(more_3ctn >= 3, more_3ctn - 2, 0)) * 1000 as forfeitSum30
              , sum(if(more_3ctn >= 3, 1, 0))                    as more_3ctn_30
         from (
                  select final_sign_network_code
                       , date_sub(date_time, rn) as diff_date
                       , count(1)                as more_3ctn
                       , frequency30
                  from (
                           select final_sign_network_code
                                , date_time
                                , row_number() over (partition by final_sign_network_code order by date_time asc) as rn
                                , count(1) over (partition by final_sign_network_code )                           as frequency30
                           from jms_dm.dm_mng_explosion_warehouse_network_punish_dt
                           where date_time >= date_add('{{ execution_date | cst_ds }}', -31)
                             and date_time <= '{{ execution_date | cst_ds }}'
                             and button_delete = 0
                       ) t
                  group by final_sign_network_code, diff_date, frequency30
              ) t
         group by final_sign_network_code, frequency30
     ) t2
     on t1.final_sign_network_code = t2.final_sign_network_code
         left join
     (
         select final_sign_network_code
              , duration
              , max_date_time
              , more_3ctn_60
         from (
                  select final_sign_network_code
                       , maxdate_time                                                                 as max_date_time
                       , last_value(duration)
                                    over (partition by final_sign_network_code order by maxdate_time) as duration
                       , sum(if(duration >= 3, 1, 0))
                             over (partition by final_sign_network_code )                             as more_3ctn_60
                  from (
                           select final_sign_network_code
                                , date_sub(date_time, rn) as diff_date
                                , count(1)                as duration
                                , max(date_time)          as maxdate_time
                                , min(date_time)          as mindate_time
                           from (
                                    select final_sign_network_code
                                         , date_time
                                         , row_number()
                                            over (partition by final_sign_network_code order by date_time asc) as rn
                                    from jms_dm.dm_mng_explosion_warehouse_network_punish_dt
                                    where date_time >= date_add('{{ execution_date | cst_ds }}', -61)
                                      and date_time <= '{{ execution_date | cst_ds }}'
                                      and button_delete = 0
                                ) t
                           group by final_sign_network_code, diff_date
                       ) t
              ) t
         where max_date_time = '{{ execution_date | cst_ds }}'
     ) t3
     on t1.final_sign_network_code = t3.final_sign_network_code
    distribute by dt
;

with day as (
    select final_sign_network_code,
           end_agent_code,
           end_agent_name,
           nvl(virt_code, end_agent_code) as virt_code,
           nvl(virt_name, end_agent_name) as virt_name,
           end_provider_id,
           end_provider_name,
           district_code,
           district_desc,
           area_code,
           area_name
    from jms_dm.dm_mng_explosion_warehouse_network_punish_dt
    where dt = '{{ execution_date | cst_ds }}'
      and button_delete = 0
    group by final_sign_network_code,
             end_agent_code,
             end_agent_name,
             virt_code,
             virt_name,
             end_provider_id,
             end_provider_name,
             district_code,
             district_desc,
             area_code,
             area_name
),
     last_day as (
         select final_sign_network_code,
                end_agent_code,
                end_agent_name,
                nvl(virt_code, end_agent_code) as virt_code,
                nvl(virt_name, end_agent_name) as virt_name,
                end_provider_id,
                end_provider_name,
                district_code,
                district_desc,
                area_code,
                area_name
         from jms_dm.dm_mng_explosion_warehouse_network_punish_dt
         where dt = date_add('{{ execution_date | cst_ds }}', -1)
           and button_delete = 0
         group by final_sign_network_code,
                  end_agent_code,
                  end_agent_name,
                  virt_code,
                  virt_name,
                  end_provider_id,
                  end_provider_name,
                  district_code,
                  district_desc,
                  area_code,
                  area_name
     ),
     union_all_tmp as (
         select nvl(day.end_agent_code, last_day.end_agent_code)       as end_agent_code
              , nvl(day.end_agent_name, last_day.end_agent_name)       as end_agent_name
              , nvl(day.virt_code, last_day.virt_code)                 as virt_code
              , nvl(day.virt_name, last_day.virt_name)                 as virt_name
              , nvl(day.end_provider_id, last_day.end_provider_id)     as end_provider_id
              , nvl(day.end_provider_name, last_day.end_provider_name) as end_provider_name
              , nvl(day.district_code, last_day.district_code)         as district_code
              , nvl(day.district_desc, last_day.district_desc)         as district_desc
              , nvl(day.area_code, last_day.area_code)                 as area_code
              , nvl(day.area_name, last_day.area_name)                 as area_name
              , day.final_sign_network_code
              , last_day.final_sign_network_code                       as last_final_sign_network_code
         from day
                  full join last_day
                            on day.final_sign_network_code = last_day.final_sign_network_code
         where (day.final_sign_network_code is not null
             or last_day.final_sign_network_code is not null)
     ),
     result_a as (
         select virt_code                                               as virt_code
              , max(virt_name)                                          as virt_name
              , virt_code                                               as agent_code
              , max(virt_name)                                          as agent_name
              , sum(if(final_sign_network_code is not null, 1, 0))      as network_cnt
              , sum(if(last_final_sign_network_code is not null, 1, 0)) as before_networ_cnt
              , sum(
                 if(final_sign_network_code is not null and last_final_sign_network_code is null, 1,
                    0))                                                 as incr_networ_cnt
              , sum(
                 if(final_sign_network_code is null and last_final_sign_network_code is not null, 1,
                    0))                                                 as low_networ_cnt
              , '虚拟代理区'                                                 as agent_type
         from union_all_tmp
         where virt_code is not null
         group by virt_code
         union all
         select virt_code
              , max(virt_name)                                          as virt_name
              , end_agent_code                                          as agent_code
              , max(end_agent_name)                                     as agent_name
              , sum(if(final_sign_network_code is not null, 1, 0))      as network_cnt
              , sum(if(last_final_sign_network_code is not null, 1, 0)) as before_networ_cnt
              , sum(
                 if(final_sign_network_code is not null and last_final_sign_network_code is null, 1,
                    0))                                                 as incr_networ_cnt
              , sum(
                 if(final_sign_network_code is null and last_final_sign_network_code is not null, 1,
                    0))                                                 as low_networ_cnt
              , '代理区'                                                   as agent_type
         from union_all_tmp
         where end_agent_code is not null
         group by end_agent_code, virt_code
         union all
         select virt_code
              , max(virt_name)                                          as virt_name
              , end_provider_id                                         as agent_code
              , max(end_provider_name)                                  as agent_name
              , sum(if(final_sign_network_code is not null, 1, 0))      as network_cnt
              , sum(if(last_final_sign_network_code is not null, 1, 0)) as before_networ_cnt
              , sum(
                 if(final_sign_network_code is not null and last_final_sign_network_code is null, 1,
                    0))                                                 as incr_networ_cnt
              , sum(
                 if(final_sign_network_code is null and last_final_sign_network_code is not null, 1,
                    0))                                                 as low_networ_cnt
              , '省份'                                                    as agent_type
         from union_all_tmp
         where end_provider_id is not null
         group by end_provider_id, virt_code
         union all
         select virt_code
              , max(virt_name)                                          as virt_name
              , district_code                                           as agent_code
              , max(district_desc)                                      as agent_name
              , sum(if(final_sign_network_code is not null, 1, 0))      as network_cnt
              , sum(if(last_final_sign_network_code is not null, 1, 0)) as before_networ_cnt
              , sum(
                 if(final_sign_network_code is not null and last_final_sign_network_code is null, 1,
                    0))                                                 as incr_networ_cnt
              , sum(
                 if(final_sign_network_code is null and last_final_sign_network_code is not null, 1,
                    0))                                                 as low_networ_cnt
              , '片区'                                                    as agent_type
         from union_all_tmp
         where district_code is not null
         group by district_code, virt_code
         union all
         select virt_code
              , max(virt_name)                                          as virt_name
              , area_code                                               as agent_code
              , max(area_name)                                          as agent_name
              , sum(if(final_sign_network_code is not null, 1, 0))      as network_cnt
              , sum(if(last_final_sign_network_code is not null, 1, 0)) as before_networ_cnt
              , sum(
                 if(final_sign_network_code is not null and last_final_sign_network_code is null, 1,
                    0))                                                 as incr_networ_cnt
              , sum(
                 if(final_sign_network_code is null and last_final_sign_network_code is not null, 1,
                    0))                                                 as low_networ_cnt
              , '网管片区'                                                  as agent_type
         from union_all_tmp
         where area_code is not null
         group by area_code, virt_code
     )
insert
overwrite
table
jms_dm.dm_sqs_abnormal_network_cnt_dt
partition
(
dt
,
dy
)
select agent_code
     , agent_name
     , network_cnt
     , before_networ_cnt
     , incr_networ_cnt
     , ((network_cnt - before_networ_cnt) / before_networ_cnt) * 100 network_cnt_rt
     , low_networ_cnt
     , 0               as                                            target_value
     , network_cnt - 0 as                                            over_value
     , '爆仓2'           as                                            data_type
     , '{{ execution_date | cst_ds }}'    as                                            date_time
     , agent_type
     , virt_code
     , virt_name
     , '{{ execution_date | cst_ds }}'    as                                            dt
     , 'baocang2'      as                                            dy
from result_a
    distribute by dt
;


